package org.xxkk.utils.bo;

import org.apache.poi.ss.usermodel.*;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;
import org.xxkk.utils.util.JsonUtils;
import org.xxkk.utils.enums.RowFlag;
import org.xxkk.utils.vo.KeyVal;

import java.io.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author xxkk
 * @version 1.0.0
 * @title ExcelSheetInfo
 * @description ʕ•ᴥ•ʔ
 * @create 2023/12/26 10:00
 **/
public class ExcelSheetInfo {
    private String output_file_name;// 输出文件名称
    private String fileName;
    private Sheet sheet;
    private Workbook workbook;
    private String function_name;// 文件功能名
    private String xml_root_name;// this.xml_element_function_name + "Root";
    private List<String> name_row;// excel第3行 属性名称
    private int row_num;// 表格最后一行
//    private List<List<KeyVal>> keyValues = null;

    private ExcelParser excelParser = null;

    public ExcelSheetInfo(String fileName, Sheet sheet, Workbook workbook, ExcelParser parser) {
        this.fileName = fileName;
        this.sheet = sheet;
        this.workbook = workbook;
        this.function_name = this.getCellContent(RowFlag.Root_row.code, 1).split("\\ +")[1];// 表首行第二单元格
        this.xml_root_name = this.function_name + "Root";
        this.name_row = this.getCellValues(RowFlag.Param_name_row.code);
        this.row_num = this.sheet.getLastRowNum();
        this.output_file_name = this.getCellContent(RowFlag.Root_row.code, 0).split("\\ +")[1];
        this.excelParser = parser;
    }

    /**
     * 获取excel单元格内容
     *
     * @param rowIndex
     * @param cellIndex
     * @return
     */
    public String getCellContent(int rowIndex, int cellIndex) {
        Cell cell = this.sheet.getRow(rowIndex).getCell(cellIndex);
        if (cell == null)
            return null;
        return cellVal2Str(cell);
    }

    private List<String> getCellValues(int rowNum) {
        List<String> val = new ArrayList<>();
        Row row = this.sheet.getRow(rowNum);
        short minColIx = row.getFirstCellNum();
        short maxColIx = row.getLastCellNum();
        for (short colIx = minColIx; colIx < maxColIx; colIx++) {
            Cell cell = row.getCell(colIx);
            if (cell == null) {
                continue;// val.add("null");
            }
            String res = cellVal2Str(cell);
            val.add(res);
        }
        return val;
    }

    /**
     * 依据过滤条件 提取excel数据
     *
     * @param filter 过滤条件枚举 前端:front 后端:back
     * @return 返回以 属性名称：cell值 键值对形式的数组
     */
    private List<List<KeyVal>> getKVLL(String filter) {
//        if (this.keyValues != null)
//            return this.keyValues;
        List<List<KeyVal>> keyValuesList = new ArrayList<>();
        for (int rowNumInc = RowFlag.Param_init_row.code; rowNumInc < this.row_num; ++rowNumInc) {
            Row row = sheet.getRow(rowNumInc);
            List<KeyVal> keyValues = new ArrayList<>();
            for (int cellNumInc = 0; cellNumInc < row.getLastCellNum(); ++cellNumInc) {
                String filters = this.getCellContent(RowFlag.Filter_row.code, cellNumInc);// 用来判断 front back i18n 作为过滤条件
                if (filters == null || !filters.contains(filter)) {// 检查是否为back等 若否 则过滤
                    continue;
                }
                String key = this.getCellContent(RowFlag.Param_name_row.code, cellNumInc);
                if (key == null) {// 检测属性名是否存在 不存在则过滤
                    continue;
                }
                String val = this.getCellContent(rowNumInc, cellNumInc);
                if (val == null)
                    throw new RuntimeException("文件 " + fileName + " 属性 " + key + " 参数值不存在 位置:" + (rowNumInc + 1) + "行 " + (cellNumInc + 1) + "列");
                KeyVal temp = new KeyVal(key, val);
                temp.val_row = rowNumInc;// 提供支持，译文需要获取所在行第一列的id
                keyValues.add(temp);
            }
            if (keyValues.isEmpty())
                continue;
            keyValuesList.add(keyValues);
        }
//        if(!keyValuesList.isEmpty())
//        this.keyValues = keyValuesList;
        return keyValuesList;
    }

    /**
     * 获取 参数名称:类型 键值对
     *
     * @param filter 过滤条件
     * @return
     */
    private List<KeyVal> get_ParamName_Type(String filter) {
        List<KeyVal> keyVals = new ArrayList<>();
        Row row = sheet.getRow(RowFlag.Param_name_row.code);
        for (int cell_index = 0; cell_index < row.getLastCellNum(); ++cell_index) {
            String filters = this.getCellContent(RowFlag.Filter_row.code, cell_index);
            if (filters == null || !filters.contains(filter)) {
                continue;
            }
            String key = this.getCellContent(RowFlag.Param_name_row.code, cell_index);
            if (key == null) {// 检测属性名是否存在 不存在则过滤
                continue;
            }
            String param_type = this.getCellContent(RowFlag.Param_type_row.code, cell_index);
            if (param_type == null)
                throw new RuntimeException("文件 " + fileName + " 属性 " + key + " 参数类型不存在 位置:" + (RowFlag.Param_type_row.code + 1) + "行 " + (cell_index + 1) + "列");
            keyVals.add(new KeyVal(key, param_type));
        }
        return keyVals;
    }

    public void parseI18n(ExcelParser excelParser) {// 涉及多线程并发写入同一个文件
        List<List<KeyVal>> kvLL = this.getKVLL("i18n");// 挑选译文内容的单元格数据（i18n）
        if (kvLL.isEmpty()){
//            kvLL.forEach((temp)->{System.out.println(temp.toString());});
            return;// 没有译文内容
        }

        //译文整理
        Map<String, List<KeyVal>> kvLMap = new HashMap<>();
        for (List<KeyVal> kvL : kvLL) {
            for (KeyVal kv : kvL) {
                List<KeyVal> kvl_temp = kvLMap.get(kv.key);
                if (kvl_temp == null) {
                    kvl_temp = new ArrayList<>();
                }
                kvl_temp.add(kv);
                kvLMap.put(kv.key, kvl_temp);
            }
        }
        // 循环写入多语言配置文件
        for (List<KeyVal> keyValList : kvLMap.values()) {
            String i18nFileName = keyValList.get(0).key;//获取多语言的文件名 ch\en\..
            BufferedWriter bw = excelParser.getI18nWriter(i18nFileName);
            /**"root": {
             "id": "value",
             "id": "你好",
             },*/
            StringBuffer sb = new StringBuffer().append("\t\"").append(this.function_name).append("\": {\n");
            for (KeyVal keyVal : keyValList) {
                String id = getCellContent(keyVal.val_row, 0);
                sb.append("\t\t\"").append(id).append("\": \"").append(keyVal.val).append("\",\n");
            }
            sb.append("\t},\n");
            try {
                bw.write(sb.toString());// 线程安全
                bw.flush();
            } catch (IOException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
    }

    public void parseDTs(BufferedWriter bw) {// 涉及多线程并发写入同一个文件
        List<KeyVal> paramKV = this.get_ParamName_Type("front");// 获取param_name : type_name 键值对
        if (paramKV.isEmpty())
            return;
        StringBuffer sb = this.param2TsStr(paramKV);// 定义ts
        try {
            bw.write(sb.toString());// 线程安全
            bw.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    public void parseTs(String pathFile) {
        List<KeyVal> paramKV = this.get_ParamName_Type("front");// 获取param_name : type_name 键值对
        if (paramKV.isEmpty())
            return;
        StringBuffer sb = this.param2TsStr(paramKV);// 定义ts
        this.writerStr2File(pathFile, sb.toString());// 写入文件
    }

    /**
     * 将键值对转为ts接口类，使得使用json数据时有代码提示
     *
     * @param paramKV 键值对数组
     * @return StringBuffer
     */
    private StringBuffer param2TsStr(List<KeyVal> paramKV) {
        String interface_name = this.function_name.substring(0, 1).toUpperCase() + this.function_name.substring(1);
        StringBuffer sb = new StringBuffer()
                .append("export default interface ")
                .append(interface_name)
                .append("{\n");
        for (KeyVal keyVal : paramKV) {
            sb.append("\t")
                    .append(keyVal.key)
                    .append(":")
                    .append(keyVal.val)
                    .append(";\n");
        }
        sb.append("}\n");
        return sb;
    }

    /**
     * 将当前工作表sheet转换成json格式
     *
     * @param pathFile json文件输出的文件绝对路径+文件名+文件格式(.json)
     */
    public void parseJson(String pathFile) {
        List<List<KeyVal>> keyValuesList = this.getKVLL("front");
        if (keyValuesList.isEmpty())
            return;
        String str = JsonUtils.toJsonArray(keyValuesList);
        this.writerStr2File(pathFile, str);
    }

    private void writerStr2File(String pathFile, String str) {
        File file = new File(pathFile);
        try {
            OutputStreamWriter outputStreamWriter = new OutputStreamWriter(new FileOutputStream(file), ExcelParser.format);
            BufferedWriter bw = new BufferedWriter(outputStreamWriter);
            bw.write(str);
            bw.flush();
            outputStreamWriter.close();
            bw.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    public void parseXML(String outputPathFile) {
        List<List<KeyVal>> keyValuesList = this.getKVLL("back");
        if (keyValuesList.isEmpty())
            return;

        //创建document并注入excel数据 createDom
        Document document = DocumentHelper.createDocument();
        Element root = document.addElement(this.xml_root_name);
        for (List<KeyVal> keyValues : keyValuesList) {
            Element elements = root.addElement(this.function_name);
            for (KeyVal keyVal : keyValues) {
                elements.addElement(keyVal.key).setText(keyVal.val);
            }
        }
        //输出dom文件（xml格式） writeDom
        OutputFormat format = OutputFormat.createPrettyPrint();
        format.setEncoding(ExcelParser.format);
        try {
            OutputStreamWriter outputStreamWriter = new OutputStreamWriter(new FileOutputStream(outputPathFile), ExcelParser.format);
            XMLWriter writer = new XMLWriter(outputStreamWriter, format);
            writer.write(document);
            outputStreamWriter.close();
            writer.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }


    /**
     * 将单元格内容转换为string
     */
    public static String cellVal2Str(Cell cell) {
        String cellValue = null;
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                double numericCellValue = cell.getNumericCellValue();
                DecimalFormat df = new DecimalFormat("#");
                cellValue = df.format(numericCellValue);
                break;
            case STRING:
            case FORMULA://cell.getCellFormula() + " ";// 公式
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN:
                String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK:
                break;
            default:
                System.out.println("获取cell异常");
        }
        return cellValue;
    }

    // 新方法 备用 如果cell转string存在问题则使用这个
    private String getCellValue(final Cell cell, final int count) {
        String cellValue = null;
        final CellType ct = cell.getCellType();
        switch (ct) {
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK:
                if (count > -1) {
                    cellValue = "BLANK" + String.valueOf(count);
                }
                break;
            case FORMULA:
                final CellType cacheCellType = cell.getCachedFormulaResultType();
            {
                switch (cacheCellType) {
                    case STRING:
                        cellValue = cell.getStringCellValue();
                        break;
                    case NUMERIC:
                        cellValue = String.valueOf(cell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        cellValue = String.valueOf(cell.getBooleanCellValue());
                        break;
                    default:
                        cellValue = cell.getCellFormula();
                }
            }
            break;
            default:
                cellValue = null;
        }
        return cellValue;
    }

    public void close() {
        try {
            this.workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    @Override
    public String toString() {
        return "ExcelSheetInfo{" +
                "fileName='" + fileName + '\'' +
                ", sheet=" + sheet +
                ", workbook=" + workbook +
                ", xml_element_name='" + function_name + '\'' +
                ", xml_root_name='" + xml_root_name + '\'' +
                ", name_row=" + name_row +
                ", row_num=" + row_num +
                '}';
    }

    public String toString2() {
        return "{" +
                "fileName='" + fileName + '\'' +
                ", xml_element_name='" + function_name + '\'' +
                ", row_num=" + row_num +
                ", name_row=" + name_row +
                '}';
    }

    public String getFileName() {
        return fileName;
    }

    public String getOutput_file_name() {
        return output_file_name;
    }
}
